package com.example.teaching_server.repository;

import com.example.teaching_server.entity.ActivityEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

/**
 * 活动数据访问层
 */
@Repository
public interface ActivityRepository extends JpaRepository<ActivityEntity, Long> {

    /**
     * 查询用户今日有效的活动
     */
    @Query("SELECT a FROM ActivityEntity a " +
            "JOIN ActivityParticipantEntity p ON a.id = p.activityId " +
            "WHERE p.userId = :userId " +
            "AND a.startDate <= CURRENT_DATE " +
            "AND a.endDate >= CURRENT_DATE")
    List<ActivityEntity> findTodayActivities(@Param("userId") Long userId);


    /**
     * 查询用户参与的最新活动（限制数量）
     */
    @Query(value = """
        SELECT a.* 
        FROM activities a
        JOIN activity_participants p ON a.id = p.activity_id
        WHERE p.user_id = :userId
        ORDER BY a.start_date DESC
        LIMIT :limit
        """, nativeQuery = true)
    List<ActivityEntity> findMyActivities(@Param("userId") Long userId, @Param("limit") int limit);

    /**
     * 查询最新的活动（限制数量）
     */
    @Query(value = """
        SELECT a.* 
        FROM activities a
        ORDER BY a.start_date DESC
        LIMIT :limit
        """, nativeQuery = true)
    List<ActivityEntity> findAllActivitiesLimit(@Param("limit") int limit);

    /**
     * 查询最新的2个活动
     */
    @Query(value = "SELECT * FROM activities ORDER BY start_date DESC LIMIT 2", nativeQuery = true)
    List<ActivityEntity> findTop2ByOrderByStartDateDesc();

    /**
     * 查询用户在指定日期范围内有效的活动
     */
    @Query(value = """
    SELECT a.* FROM activities a
    JOIN activity_participants p ON a.id = p.activity_id
    WHERE p.user_id = :userId
    AND a.start_date <= :endDate
    AND a.end_date >= :startDate
    """, nativeQuery = true)
    List<ActivityEntity> findActivitiesByDateRange(
            @Param("userId") Long userId,
            @Param("startDate") LocalDate startDate,
            @Param("endDate") LocalDate endDate);

    /**
     * 查询用户在指定日期有效的活动
     */
    @Query(value = """
    SELECT a.* FROM activities a
    JOIN activity_participants p ON a.id = p.activity_id
    WHERE p.user_id = :userId
    AND a.start_date <= :date
    AND a.end_date >= :date
    """, nativeQuery = true)
    List<ActivityEntity> findValidActivitiesByDate(
            @Param("userId") Long userId,
            @Param("date") LocalDate date);

    /**
     * 根据关键词搜索活动
     */
    @Query(value = """
    SELECT a.* FROM activities a
    WHERE a.title LIKE CONCAT('%', :keyword, '%')
       OR a.base_name LIKE CONCAT('%', :keyword, '%')
       OR a.address LIKE CONCAT('%', :keyword, '%')
       OR a.description LIKE CONCAT('%', :keyword, '%')
       OR a.target_audience LIKE CONCAT('%', :keyword, '%')
    ORDER BY a.start_date DESC
    """, nativeQuery = true)
    List<ActivityEntity> findByKeyword(@Param("keyword") String keyword);

    /**
     * 根据搜索条件查询活动
     */
    @Query(value = """
    SELECT a.* FROM activities a 
    WHERE 
        (:keyword IS NULL OR :keyword = '' OR 
         a.title LIKE CONCAT('%', :keyword, '%') OR 
         a.description LIKE CONCAT('%', :keyword, '%') OR 
         a.target_audience LIKE CONCAT('%', :keyword, '%')) 
        AND (:baseId IS NULL OR a.base_id = :baseId)
        AND (:fee IS NULL OR :fee = '' OR 
             (:fee = 'free' AND a.is_free = true) OR 
             (:fee = 'paid' AND a.is_free = false)) 
    ORDER BY a.publish_time DESC
    """, nativeQuery = true)
    List<ActivityEntity> findBySearchCriteria(
            @Param("keyword") String keyword,
            @Param("baseId") Long baseId,
            @Param("fee") String fee);

    /**
     * 根据搜索条件查询活动（包含创建者信息）
     */
    @Query(value = """
    SELECT a.*, u.name as creator_name, u.phone as creator_phone, u.email as creator_email 
    FROM activities a 
    LEFT JOIN users u ON a.creator_id = u.id
    WHERE 
        (:keyword IS NULL OR :keyword = '' OR 
         a.title LIKE CONCAT('%', :keyword, '%') OR 
         a.description LIKE CONCAT('%', :keyword, '%') OR 
         a.target_audience LIKE CONCAT('%', :keyword, '%')) 
        AND (:baseId IS NULL OR a.base_id = :baseId) 
        AND (:fee IS NULL OR :fee = '' OR 
             (:fee = 'free' AND a.is_free = true) OR 
             (:fee = 'paid' AND a.is_free = false)) 
    ORDER BY a.publish_time DESC
    """, nativeQuery = true)
    List<Object[]> findBySearchCriteriaWithCreatorNative(
            @Param("keyword") String keyword,
            @Param("baseId") Long baseId,
            @Param("fee") String fee);

    /**
     * 根据搜索条件查询活动（返回Map格式）
     */
    @Query(value = """
    SELECT 
        a.id, a.title, a.description, a.start_date, a.end_date, a.total_days,
        a.address, a.start_time, a.registration_deadline, a.target_audience, 
        a.is_free, a.publish_time, a.base_id, a.base_name, a.max_participants, 
        a.current_participants, a.creator_id, u.name as creator_name, 
        u.phone as creator_phone, u.email as creator_email
    FROM activities a 
    LEFT JOIN users u ON a.creator_id = u.id
    WHERE 
        (:keyword IS NULL OR :keyword = '' OR 
         a.title ILIKE CONCAT('%', :keyword, '%') OR 
         a.description ILIKE CONCAT('%', :keyword, '%') OR 
         a.target_audience ILIKE CONCAT('%', :keyword, '%')) 
        AND (:baseId IS NULL OR a.base_id = :baseId) 
        AND (:fee IS NULL OR :fee = '' OR 
             (:fee = 'free' AND a.is_free = true) OR 
             (:fee = 'paid' AND a.is_free = false)) 
    ORDER BY a.publish_time DESC
    """, nativeQuery = true)
    List<Map<String, Object>> findBySearchCriteriaWithCreatorNativeMap(
            @Param("keyword") String keyword,
            @Param("baseId") Long baseId,
            @Param("fee") String fee);

    /**
     * 检查用户是否参与活动（JPA方式）
     */
    @Query("SELECT COUNT(ap) > 0 FROM ActivityParticipantEntity ap " +
            "WHERE ap.userId = :userId AND ap.activityId = :activityId")
    boolean isUserJoinedActivity(@Param("userId") Long userId,
                                 @Param("activityId") Long activityId);

    /**
     * 检查用户是否参与活动（原生SQL方式）
     */
    @Query(value = "SELECT COUNT(*) > 0 FROM activity_participants " +
            "WHERE user_id = :userId AND activity_id = :activityId",
            nativeQuery = true)
    boolean isUserJoinedActivityNative(@Param("userId") Long userId,
                                       @Param("activityId") Long activityId);

    /**
     * 根据活动ID列表查询活动（包含创建者信息）
     */
    @Query("SELECT a FROM ActivityEntity a " +
            "LEFT JOIN FETCH a.creator c " +
            "WHERE a.id IN :activityIds " +
            "ORDER BY a.publishTime DESC")
    List<ActivityEntity> findActivitiesByIdsWithCreator(@Param("activityIds") List<Long> activityIds);

    /**
     * 查询用户参与的活动ID列表
     */
    @Query("SELECT ap.activityId FROM ActivityParticipantEntity ap WHERE ap.userId = :userId")
    List<Long> findJoinedActivityIds(@Param("userId") Long userId);

    /**
     * 查询用户参与的符合搜索条件的活动
     */
    @Query("SELECT a FROM ActivityEntity a " +
            "LEFT JOIN FETCH a.creator c " +
            "WHERE a.id IN :activityIds " +
            "AND (:keyword IS NULL OR :keyword = '' OR " +
            "     a.title LIKE CONCAT('%', :keyword, '%') OR " +
            "     a.description LIKE CONCAT('%', :keyword, '%') OR " +
            "     a.targetAudience LIKE CONCAT('%', :keyword, '%')) " +
            "AND (:baseId IS NULL OR a.baseId = :baseId) " +
            "AND (:fee IS NULL OR :fee = '' OR " +
            "     (:fee = 'free' AND a.isFree = true) OR " +
            "     (:fee = 'paid' AND a.isFree = false)) " +
            "ORDER BY a.publishTime DESC")
    List<ActivityEntity> findJoinedActivitiesWithSearch(
            @Param("activityIds") List<Long> activityIds,
            @Param("keyword") String keyword,
            @Param("baseId") Long baseId,
            @Param("fee") String fee);

    /**
     * 查询用户发布的活动（符合搜索条件）
     */
    @Query("SELECT a FROM ActivityEntity a " +
            "LEFT JOIN FETCH a.creator c " +
            "WHERE a.creatorId = :userId " +
            "AND (:keyword IS NULL OR :keyword = '' OR " +
            "     a.title LIKE CONCAT('%', :keyword, '%') OR " +
            "     a.description LIKE CONCAT('%', :keyword, '%') OR " +
            "     a.targetAudience LIKE CONCAT('%', :keyword, '%')) " +
            "AND (:baseId IS NULL OR a.baseId = :baseId) " +
            "AND (:fee IS NULL OR :fee = '' OR " +
            "     (:fee = 'free' AND a.isFree = true) OR " +
            "     (:fee = 'paid' AND a.isFree = false)) " +
            "ORDER BY a.publishTime DESC")
    List<ActivityEntity> findPublishedActivitiesWithSearch(
            @Param("userId") Long userId,
            @Param("keyword") String keyword,
            @Param("baseId") Long baseId,
            @Param("fee") String fee);

    /**
     * 根据班级ID查询活动
     */
    @Query(value = "SELECT a.* FROM activities a WHERE a.class_id = :classId ORDER BY a.start_date DESC",
            nativeQuery = true)
    List<ActivityEntity> findByClassId(@Param("classId") Long classId);

    /**
     * 查询最新的N个活动
     */
    @Query(value = "SELECT * FROM activities ORDER BY start_date DESC LIMIT :limit", nativeQuery = true)
    List<ActivityEntity> findTopNByOrderByStartDateDesc(@Param("limit") int limit);
}
